﻿    --使用SQL语句（以后所有作业均是要求使用SQL语句完成，不再额外声明）

    --    1.新建一个数据库：17bang，能指定/查看该数据库存放位置
    --    2.依次备份/删除/恢复该数据库
 CREATE DATABASE [17bang];
 --C:\Users\89305\17bang.mdf
 BACKUP DATABASE [17bang] TO DISK = 'D:\17bang.bak'

 DROP DATABASE [17bang];

 RESTORE DATABASE [17bang] FROM DISK = 'D:\17bang.bak'

 
    --观察“一起帮”的：
    --    1.注册和发布求助功能，试着建表User：包含UserName（用户名），Password（密码）
    --    2.内容发布功能：试着建表Keyword：包含Name（名称），Used（使用次数）
    --为User表添加一列：邀请人（InvitedBy），类型为INT
    --将InvitedBy类型修改为NVARCHAR(10)
    --删除列InvitedBy
USE [17bang];
CREATE TABLE [User]
(
    [UserName] NCHAR(10) NULL,
    [Password] NCHAR(20) NULL,
)
CREATE TABLE [KeyWord]
(
    [Name] NCHAR(10) NULL,
    [Used] INT NULL,
)
ALTER TABLE [User] ADD InvitedBy INT;
ALTER TABLE [User] ALTER COLUMN InvitedBy NVARCHAR(10);
ALTER TABLE [User] DROP COLUMN InvitedBy;

    -- 观察“一起帮”的发布求助功能，试着建立表Problem，包含：
    --    Id
    --    Title（标题）
    --    Content（正文）
    --    NeedRemoteHelp（需要远程求助）
    --    Reward（悬赏）
    --    PublishDateTime（发布时间）……
    --请为这些列选择合适的数据类型。
USE [17bang];
CREATE TABLE [Problem]
(
    [Id] INT NULL,
    [Title] NVARCHAR(50) NULL,
    [Content] NTEXT NULL,
    [NeedRemoteHelp] NVARCHAR(4000) NULL,
    [Reward] INT NULL,
    [PublishDateTime] DATETIME NULL,
)
ALTER TABLE Problem
ALTER COLUMN NeedRemoteHelp BIT NULL;
SELECT * FROM [Problem]

    --1.在User表中插入以下四行数据：
    --UserName:17bang、 Admin、 Admin-1、 SuperAdmin  
    --Password:1234、   NULL、      、    123456
    --2.将Problem表中的Reward全部更新为0
    --3.使用事务，
    --    1）删除User表中的全部数据，
    --    2）回滚事务，撤销之前的删除行为

SELECT * FROM [User];
INSERT [User] ([UserName],[Password]) VALUES (N'17bang',1234);
INSERT [User] ([UserName]) VALUES (N'Admin');
INSERT [User] ([UserName],[Password]) VALUES (N'Admin-1','   '); 
INSERT [User] ([UserName],[Password]) VALUES (N'SuperAdmin',123456);

UPDATE Problem SET Reward = 0;

BEGIN TRANSACTION;
DELETE [User];
ROLLBACK;

    -- 在User表中：
    --    1.查找没有录入密码的用户
    --    2.删除用户名（UserName）中包含“Admin”或者“17bang”字样的用户
USE [17bang];
SELECT * FROM [User]
WHERE Password IS  NULL;

BEGIN TRAN;
DELETE [User] WHERE (UserName LIKE '%Admin%') OR (UserName LIKE '%17bang%');
COMMIT;
    --在Problem表中：
    --    1.给所有悬赏（Reward）大于10的求助标题加前缀：【推荐】
    --    2.给所有悬赏大于20且发布时间（Created）在2019年10月10日之后的求助标题加前缀：【加急】
    --    3.删除所有标题以中括号【】开头（无论其中有什么内容）的求助
    --    4.查找Title中第5个起，字符不为“数据库”且包含了百分号（%）的求助
SELECT * FROM [Problem]
BEGIN TRAN;
INSERT [Problem](Id,Title,Content,NeedRemoteHelp,Reward,PublishDateTime) 
VALUES(1,N'SQL异常无法处理',N'吧啦吧啦吧啦...',1,15,'2020/5/18 17:09:23');
INSERT [Problem] VALUES(2,N'怎么写好代码啊啊啊！',N'今天...',0,8,'2019/7/15 20:00:01');
INSERT [Problem] VALUES(3,N'自闭关上C#的大门',N'各位大佬吧啦吧啦...',1,10,'2020/9/29 12:04:59');
INSERT [Problem] VALUES(4,N'考试如何快速抱C语言佛脚',N'救救孩子明天考试了...',0,25,'2021/2/14 22:50:12');
INSERT [Problem] VALUES(5,N'8.0版本Visual Studio疑惑求解答',N'.......',1,11,'2020/11/11 11:11:11');
INSERT [Problem] VALUES(6,N'源栈学生就业率有90%吗',N'源栈大飞哥回答...',0,5,'2021/04/06 22:00:12');
ROLLBACK;
COMMIT;
BEGIN TRAN
UPDATE [Problem] SET Title = N'【推荐】' + Title 
WHERE Reward > 10;
UPDATE [Problem] SET Title = N'【加急】' + Title
WHERE (Reward > 20) AND (PublishDateTime > '2019/10/10');
DELETE Problem WHERE Title LIKE N'【%】%'; 
--4.查找Title中第5个起，字符不为“数据库”且包含了百分号（%）的求助
SELECT * FROM Problem
WHERE  (Title NOT LIKE '____数据库%') AND (Title LIKE '%#%%' ESCAPE '#')

    --在Keyword表中：
    --    1.找出所有被使用次数（Used）大于10小于50的关键字名称（Name）
    --    2.如果被使用次数（Used）小于等于0，或者是NULL值，或者大于100的，将其更新为1
    --    3.删除所有使用次数为奇数的Keyword
    --注意，上述作业需要自己插入数据进行测试 
SELECT * FROM KeyWord
ALTER TABLE KeyWord ALTER COLUMN [Name] NVARCHAR(10) NULL ;
BEGIN TRANSACTION;
INSERT KeyWord([Name],Used) VALUES(N'C#',101);
INSERT KeyWord([Name],Used) VALUES(N'JAVA',71);
INSERT KeyWord([Name],Used) VALUES(N'SQL',18);
INSERT KeyWord([Name],Used) VALUES(N'JavaScript',55);
INSERT KeyWord([Name],Used) VALUES(N'html',32);
INSERT KeyWord([Name],Used) VALUES(N'法律',0);
INSERT KeyWord([Name],Used) VALUES(N'Python',20);
INSERT KeyWord([Name]) VALUES(N'.net');
COMMIT;
SELECT [Name] FROM KeyWord
WHERE (Used > 10) AND (Used < 50);

BEGIN TRAN;
UPDATE KeyWord SET Used = 1
WHERE (Used <= 0) OR (Used IS NULL) OR (Used > 100);
COMMIT;

BEGIN TRAN;
DELETE KeyWord
WHERE Used % 2 != 0;
COMMIT;

    -- 在User表上的基础上：
    --    1.添加Id列，让Id成为主键
    --    2.添加约束，让UserName不能重复
ALTER TABLE [User] ADD Id INT CONSTRAINT PK_User_Id PRIMARY KEY(Id);
ALTER TABLE [User] ADD CONSTRAINT UQ_User_UserName UNIQUE(UserName);

    --在Problem表的基础上：
    --    1.为NeedRemoteHelp添加NOT NULL约束，再删除NeedRemoteHelp上NOT NULL的约束
    --    2.添加自定义约束，让Reward不能小于10
ALTER TABLE Problem ALTER COLUMN NeedRemoteHelp BIT NOT NULL; 
ALTER TABLE Problem ALTER COLUMN NeedRemoteHelp BIT NULL;
ALTER TABLE Problem ADD CONSTRAINT CK_Problem_Reward CHECK(Reward >= 10);--冲突


    --1.观察一起帮的“关键字”功能，新建Keyword表，要求带一个自增的主键Id，起始值为10，步长为5；并存入若干条数据
    --2.将User表中Id列修改为可存储GUID的类型，并存入若干条包含GUID值的数据
    --3.Problem表已有Id列，如何给该列加上IDENTITY属性？

CREATE TABLE KeyWord
(
    Id INT IDENTITY(10,5) CONSTRAINT PK_KeyWord_Id PRIMARY KEY,
    [Name] NVARCHAR(10) NULL,
    [Used] INT NULL,
)
INSERT KeyWord VALUES(N'小甲',23);
INSERT KeyWord VALUES(N'小乙',15);
INSERT KeyWord VALUES(N'小丙',67);
INSERT KeyWord VALUES(N'小丁',45);
INSERT KeyWord VALUES(N'小戊',0);

ALTER TABLE [User] ADD Id2 VARCHAR(36);
UPDATE [User] SET Id2 = Id;
ALTER TABLE [User] DROP CONSTRAINT PK_User_Id;
ALTER TABLE [User] DROP COLUMN Id;
INSERT [User](Id2,UserName) VALUES(NEWID(),'3')
SELECT * FROM [User]

SELECT * FROM [Problem]
--删除Id列，新增Id列添加属性
ALTER TABLE Problem DROP COLUMN Id;
ALTER TABLE Problem ADD Id INT IDENTITY ;

    --1.在Problem中插入不同作者（Author）不同悬赏（Reward）的若干条数据，以便能完成以下操作：
    --    1.查找出Author为“飞哥”的、Reward最多的3条求助
    --    2.所有求助，先按作者“分组”，然后在“分组”中按悬赏从大到小排序
    --    3.查找并统计出每个作者的：求助数量、悬赏总金额和平均值
    --    4.找出平均悬赏值少于10的作者并按平均值从小到大排序
    --2.以Problem中的数据为基础，使用SELECT INTO，新建一个Author和Reward都没有NULL值的新表：NewProblem （把原Problem里Author或Reward为NULL值的数据删掉）
    --3.使用INSERT SELECT, 将Problem中Reward为NULL的行再次插入到NewProblem中
SELECT * FROM [Problem]
DELETE Problem
ALTER TABLE Problem 
DROP CONSTRAINT CK_Problem_Reward
ALTER TABLE Problem ADD Author NVARCHAR(10) NULL
UPDATE Problem SET Reward = NULL
INSERT Problem(Author,Reward) VALUES(N'飞哥',15)
INSERT Problem(Author,Reward) VALUES(N'小鱼',12)
INSERT Problem(Author,Reward) VALUES(N'飞哥',8)
INSERT Problem(Author,Reward) VALUES(N'飞哥',12)
INSERT Problem(Author,Reward) VALUES(N'小鱼',6)
INSERT Problem(Author,Reward) VALUES(N'飞哥',17)
INSERT Problem(Author,Reward) VALUES(N'小风',9)
INSERT Problem(Author,Reward) VALUES(N'小风',3)
INSERT Problem(Author,Reward) VALUES(N'小风',10)
    --    1.查找出Author为“飞哥”的、Reward最多的3条求助
    --    2.所有求助，先按作者“分组”，然后在“分组”中按悬赏从大到小排序
    --    3.查找并统计出每个作者的：求助数量、悬赏总金额和平均值
    --    4.找出平均悬赏值少于10的作者并按平均值从小到大排序
SELECT TOP 3 Reward FROM Problem
WHERE Author = N'飞哥'   
ORDER BY Reward DESC

SELECT Author,Reward FROM Problem
--GROUP BY Author,Reward 
ORDER BY Author,Reward DESC

SELECT Author, COUNT(Reward) AS [Count] FROM Problem
GROUP BY Author
SELECT Author, SUM(Reward) AS [SUM] FROM Problem
GROUP BY Author
SELECT Author, AVG(Reward) AS [AVG] FROM Problem
GROUP BY Author

SELECT Author, AVG(Reward) AS [AVG] FROM Problem
GROUP BY Author
HAVING AVG(Reward) < 10
ORDER BY AVG(Reward) 

--2.以Problem中的数据为基础，使用SELECT INTO，新建一个Author和Reward都没有NULL值的新表：NewProblem （把原Problem里Author或Reward为NULL值的数据删掉）
SELECT Author,Reward 
INTO NewProblem
FROM Problem
SELECT * FROM NewProblem

    --3.使用INSERT SELECT, 将Problem中Reward为NULL的行再次插入到NewProblem中
INSERT Problem(Author) VALUES(N'飞哥')
INSERT Problem(Author) VALUES(N'小鱼')
BEGIN TRAN
INSERT NewProblem
SELECT Author,Reward FROM Problem
WHERE Reward IS NULL

    -- 新建表Message(Id, FromUser, ToUser, UrgentLevel, Kind, HasRead, IsDelete, Content)，使用该表和SQL语句，证明：
    --    唯一约束依赖于唯一索引
    --    主键上可以是非聚集索引
    --并利用“执行计划”图演示说明：Scan、Seek和Lookup的使用和区别。

    --添加唯一约束，系统会自动添加唯一索引
    --删除唯一约束也会同时删除该约束所依赖的唯一索引
    --创建了唯一约束，查找的时候看执行计划是否使用了唯一约束中的唯一索引
USE [17bang]
DROP TABLE IF EXISTS Message;
CREATE TABLE Message
(
    Id INT NOT NULL,
    FromUser NVARCHAR(10),
    ToUser NVARCHAR(10),
    UrgentLevel NVARCHAR(20), 
    Kind INT,
    HasRead BIT,
    IsDelete BIT,
    Content TEXT
);

ALTER TABLE Message
ADD CONSTRAINT UQ_Message_FromUser UNIQUE(FromUser)

SELECT [name], [type], is_unique, is_primary_key, is_unique_constraint 
FROM sys.indexes 
WHERE object_id = OBJECT_ID('Message')

SELECT * FROM Message
--WHERE Id >1  --SCAN
WHERE FromUser = N'' --SEEK LOOKUP
--执行计划中可以看到查找FromUser时，数据库使用了唯一约束中的唯一非聚集索引，由此证明唯一约束依赖于唯一索引

    --    主键上可以是非聚集索引
 --创建主键前已经创建了聚集索引，创建主键时就会自动创建一个非聚集唯一索引
CREATE CLUSTERED INDEX IX_Message_ToUser ON Message(ToUser)
ALTER TABLE Message
ADD CONSTRAINT PK_Message_Id PRIMARY KEY(Id)

    --观察并模拟17bang项目中的：
    --    1.用户资料，新建用户资料（Profile）表，和User形成1:1关联（有无约束？）。用SQL语句演示：
    --        1.新建一个填写了用户资料的注册用户
    --        2.通过Id查找获得某注册用户及其用户资料
    --        3.删除某个Id的注册用户
--一个用户有一个用户资料表
SELECT * FROM Profile
ALTER TABLE [User]
ADD CONSTRAINT PK_User_Id PRIMARY KEY(Id)
CREATE TABLE Profile
(
    Id INT NOT NULL CONSTRAINT PK_Profile_Id PRIMARY KEY,
    ProfileName NVARCHAR(25),
    Userdate TEXT,
    UserId INT CONSTRAINT FK_Profile_UserId FOREIGN KEY REFERENCES [User](Id)
)
SELECT ProfileName,Userdate FROM Profile WHERE Id = 1 
BEGIN TRAN
DELETE Profile WHERE Id = 3
ROLLBACK

    --    2.帮帮点说明：新建Credit表，可以记录用户的每一次积分获得过程，即：某个用户，在某个时间，因为某某原因，获得若干积分
SELECT * FROM Credit
CREATE TABLE Credit
(
    Id INT CONSTRAINT PK_Credit_Id PRIMARY KEY,
    [User] INT CONSTRAINT FK_Profile_User FOREIGN KEY REFERENCES [User](Id),
    [Time] DateTime,
    Reason NVARCHAR(100),
    Number INT
)
    --    3.发布求助，在Problem和User之间建立1:n关联（含约束）。用SQL语句演示：
    --        1.某用户发布一篇求助，
    --        2.将该求助的作者改成另外一个用户
    --        3.删除该用户
--一个用户可以发布很多篇求助
SELECT * FROM Problem
SELECT * FROM [User]
ALTER TABLE Problem
--ADD UserId INT
ADD CONSTRAINT FK_User_Id FOREIGN KEY(UserId) REFERENCES [User](Id) 
INSERT Problem VALUES(N'SQL',N'吧啦吧啦吧啦吧',1,12,2020-09-12,N'飞哥',1)
UPDATE Problem SET UserId = 3 WHERE Id = 30
DELETE Profile WHERE UserId = 3
DELETE Problem WHERE UserId = 3
DELETE [User] WHERE Id = 3
    --    4.求助列表：新建Keyword表，和Problem形成n:n关联（含约束）。用SQL语句演示：
    --        1.查询获得：每个求助使用了多少关键字，每个关键字被多少求助使用
    --        2.发布了一个使用了若干个关键字的求助
    --        3.该求助不再使用某个关键字
    --        4.删除该求助
    --        5.删除某关键字

SELECT * FROM Problem
SELECT * FROM Keyword
SELECT * FROM Keyword2Problem
CREATE TABLE Keyword2Problem
(
    KeywordId INT CONSTRAINT FK_K2P_KeywordId FOREIGN KEY REFERENCES Keyword(Id),
    ProblemId INT CONSTRAINT FK_K2P_ProblemId FOREIGN KEY REFERENCES Problem(Id)
)
    --        1.查询获得：每个求助使用了多少关键字，每个关键字被多少求助使用
SELECT ProblemId,COUNT(ProblemId) AS [COUNT] FROM Keyword2Problem WHERE ProblemId = 28 
GROUP BY ProblemId   

SELECT KeywordId,COUNT(KeywordId) AS [COUNT] FROM Keyword2Problem WHERE KeywordId = 2 
GROUP BY KeywordId   
    --        2.发布了一个使用了若干个关键字的求助
INSERT Problem(Title,Content,NeedRemoteHelp,Reward,PublishDateTime,Author,UserId) 
VALUES(N'G1','.......',1,23,'2021/2/3',N'huhu',1)
INSERT Keyword2Problem VALUES(1,40)
    --        3.该求助不再使用某个关键字
DELETE Keyword2Problem WHERE KeywordId = 4 AND ProblemId = 36
    --        4.删除该求助
--先删从表再删主表  
DELETE Keyword2Problem WHERE ProblemId = 33
DELETE Problem WHERE Id = 33
    --        5.删除某关键字
DELETE Keyword2Problem WHERE KeywordId = 4
DELETE Keyword WHERE Id = 4

    --1.联表查出求助的标题和作者用户名
SELECT p.Title,u.UserName
FROM Problem p JOIN [User] u
ON p.UserId = u.Id
    --2.查找并删除从未发布过求助的用户
INSERT [User] VALUES(N'小胡',345,3)
BEGIN TRAN
DELETE Keyword2Problem
DELETE [User]
--SELECT p.Title,u.UserName 
FROM Problem p RIGHT JOIN [User] u
ON p.UserId = u.Id
WHERE p.Id IS NULL
ROLLBACK
    --3.用一句SELECT显示出用户和他的邀请人用户名
ALTER TABLE [User]
ALTER COLUMN Inviter NVARCHAR(20)
SELECT *
FROM [User] u1 JOIN [User] u2
ON u1.Id = u2.INVITER

    --4.17bang的关键字有“一级”“二级”和其他“普通（三）级”的区别：
    --    1.请在表Keyword中添加一个字段，记录这种关系
    --    2.然后用一个SELECT语句查出所有普通关键字的上一级、以及上上一级的关键字名称，比如：
SELECT * FROM KeyWord
ALTER TABLE Keyword
ADD Relaction NVARCHAR(15)

SELECT k1.[Name] AS 关键字,k2.[Name] AS 上一级,k3.[Name] AS 再上一级 
FROM Keyword k1 LEFT JOIN Keyword k2 
ON k1.Realtion = k2.Id
LEFT JOIN Keyword k3
ON k2.Relation = k3.Id

    --5.17bang中除了求助（Problem），还有意见建议（Suggest）和文章（Article），他们都包含Title、Content、PublishTime和Author四个字段，但是：
    --    1.建议和文章没有悬赏（Reward）
    --    2.建议多一个类型：Kind NVARCHAR(20)）
    --    3.文章多一个分类：Category INT）
    --请按上述描述建表。然后，用一个SQL语句显示某用户发表的求助、建议和文章的Title、Content，并按PublishTime降序排列
SELECT * FROM Problem
SELECT * FROM [User]
CREATE TABLE Suggest
(
    Title NVARCHAR(50),
    Content NTEXT,
    PublishTime DATETIME,
    Author NVARCHAR(10),
    Kind NVARCHAR(20),
    UserId INT CONSTRAINT FK_S_User_Id FOREIGN KEY REFERENCES [User](Id)
)
INSERT Suggest(Title,Content,PublishTime,Author,Kind,UserId) VALUES(N'A',N'....','2019/5/20',N'彩虹',N'',1)
INSERT Suggest(Title,Content,PublishTime,Author,Kind,UserId) VALUES(N'A',N'#...','2020/5/20',N'小马',N'',2)
INSERT Suggest(Title,Content,PublishTime,Author,Kind,UserId) VALUES(N'A',N'.$..','2021/5/20',N'彩虹',N'',1)

CREATE TABLE Article
(
    Title NVARCHAR(50),
    Content NTEXT,
    PublishTime DATETIME,
    Author NVARCHAR(10),
    Category INT,
    UserId INT CONSTRAINT FK_A_User_Id FOREIGN KEY REFERENCES [User](Id)
)
INSERT Article(Title,Content,PublishTime,Author,Category,UserId) VALUES(N'F',N'*******','2019/8/6',N'阳光',N'',2)
INSERT Article(Title,Content,PublishTime,Author,Category,UserId) VALUES(N'F',N'***：；**','2020/8/6',N'乌云',N'',2)
INSERT Article(Title,Content,PublishTime,Author,Category,UserId) VALUES(N'F',N'***（*','2021/8/6',N'阳光',N'',1)

ALTER TABLE Article
ALTER COLUMN Content NVARCHAR(4000)
    --请按上述描述建表。然后，用一个SQL语句显示某用户发表的求助、建议和文章的Title、Content，并按PublishTime降序排列
SELECT PublishDateTime,Title,Content FROM Problem
UNION
SELECT PublishTime,Title,Content FROM Suggest
UNION
SELECT PublishTime,Title,Content FROM Article
ORDER BY PublishDateTime DESC 

     --为求助添加一个发布时间（TPublishTime），使用子查询：
     --已经有 PublishDateTime
     --   删除每个作者悬赏最低的求助  作者、悬赏最低两个条件 关联子查询
BEGIN TRAN
DELETE Keyword2Problem
DELETE Problem FROM Problem op WHERE Reward =
(
    SELECT MIN(Reward) FROM Problem [ip] 
    WHERE op.Author = [ip].Author
)
ROLLBACK
     --   找到从未成为邀请人的用户 在IN里面NULL值找不到  找到有邀请人的用户
SELECT * FROM [User] WHERE Id NOT IN
(
    SELECT INVITER FROM [User]
    WHERE INVITER IS NOT NULL
)
     --   如果一篇求助的关键字大于3个，将它的悬赏值翻倍
UPDATE Problem SET Reward *= 2
WHERE Id IN
(
    SELECT ProblemId FROM Keyword2Problem     
    GROUP BY ProblemId  
    HAVING COUNT(ProblemId) >3
)
     --   查出所有发布一篇以上（不含一篇）文章的用户信息
SELECT * FROM [User] WHERE Id IN 
(
    SELECT UserId FROM Article
    GROUP BY UserId
    HAVING COUNT(UserId) > 1
)
     --   查找每个作者最近发布的一篇文章
SELECT * FROM Article
SELECT * FROM Article oa WHERE PublishTime =
(
    SELECT MAX(PublishTime) FROM Article ia
    WHERE oa.Author = ia.Author
)
     --   查出每一篇求助的悬赏都大于5个帮帮币的作者
SELECT Author FROM Problem op WHERE Reward IN
(
    SELECT MIN(Reward) FROM Problem [ip]
    WHERE op.Author = [ip].Author
    HAVING MIN(Reward) > 5
)

     --分别使用派生表和CTE，查询求助表（表中只有一列整体的发布时间，没有年月的列），以获得：
     --   1.一起帮每月各发布了求助多少篇
WITH TimeProblem
AS
(
    SELECT *,Month(PublishDateTime) AS TimeMonth,Year(PublishDateTime) AS TimeYear
    FROM Problem 
)
SELECT TimeMonth,TimeYear,COUNT(*) AS [Count] FROM TimeProblem
GROUP BY TimeProblem.TimeMonth,TimeProblem.TimeYear

     --   2.每月发布的求助中，悬赏最多的3篇
SELECT * FROM(
    SELECT ROW_NUMBER() OVER(PARTITION BY TimeMonth,TimeYear
    ORDER BY Reward DESC)AS Seq,Reward
	FROM (
        SELECT *,Month(PublishDateTime) AS TimeMonth,Year(PublishDateTime) AS TimeYear
        FROM Problem)tp) rnp
WHERE rnp.Seq <= 3

     --   3.每个作者，每月发布的，悬赏最多的3篇
SELECT * FROM(
    SELECT ROW_NUMBER() OVER(PARTITION BY TimeMonth,TimeYear,Author
    ORDER BY Reward DESC)AS Seq,Reward,Author
	FROM (
        SELECT *,Month(PublishDateTime) AS TimeMonth,Year(PublishDateTime) AS TimeYear
        FROM Problem
        )tp) rnp
WHERE rnp.Seq <= 3

   --   4.分别按发布时间和悬赏数量进行分页查询的结果
SELECT * FROM Problem ORDER BY PublishDateTime 
OFFSET 0 ROWS
FETCH NEXT 12 ROWS ONLY

SELECT * FROM Problem ORDER BY Reward 
OFFSET 0 ROWS
FETCH NEXT 12 ROWS ONLY

    --1.创建求助的应答表 TResponse(Id, Content, AuthorId, ProblemId, CreateTime)
CREATE TABLE TResponse
(
    Id INT,
    Content NTEXT ,
    AuthorId INT CONSTRAINT FK_TResponse_User_Id FOREIGN KEY REFERENCES [User](Id),
    ProblemId INT CONSTRAINT FK_TResponse_Problem_Id FOREIGN KEY REFERENCES Problem(Id),
    CreateTime DateTime
)
    --2。然后生成一个视图VResponse(ResponseId, Content, AuthorId, AuthorName, ProblemId, ProblemTitle, CreateTime)，要求该视图：
    --    能展示应答作者的用户名、应答对应求助的标题和作者用户名
    --    只显示求助悬赏值大于5的数据
    --    已被加密
    --    保证其使用的基表结构无法更改
GO
CREATE VIEW V_Response
WITH SCHEMABINDING,ENCRYPTION
AS SELECT 
    t.Id ResponseId,t.Content,t.AuthorId,u.UserName AuthorName,t.ProblemId,p.Title ProblemTitle,t.CreateTime
FROM dbo.TResponse t JOIN dbo.[User] u 
ON t.AuthorId = u.Id JOIN dbo.Problem p ON t.ProblemId = p.Id
WHERE p.Reward > 5
    --3.演示：在VResponse中插入一条数据，却不能在视图中显示
GO
SELECT * FROM Problem
SELECT * FROM [User]
SELECT * FROM V_Response
INSERT V_Response(ResponseId, Content, AuthorId, ProblemId,CreateTime)
VALUES(2,N'芝麻开门-FG',3,35,'2020/11/4')
--INSERT Problem
    --4.修改VResponse，让其能避免上述情形
GO
ALTER VIEW V_Response
WITH SCHEMABINDING,ENCRYPTION
AS SELECT 
    t.Id ResponseId,t.Content,t.AuthorId,u.UserName AuthorName,t.ProblemId,p.Title ProblemTitle,t.CreateTime
FROM dbo.TResponse t JOIN dbo.[User] u 
ON t.AuthorId = u.Id JOIN dbo.Problem p ON t.ProblemId = p.Id
WHERE p.Reward > 5
WITH CHECK OPTION
    --5.创建视图VProblemKeyword(ProblemId, ProblemTitle, ProblemReward, KeywordAmount)，要求该视图：
    --    能反映求助的标题、使用关键字数量和悬赏
    --    在ProblemId上有一个唯一聚集索引
    --    在ProblemReward上有一个非聚集索引
GO
CREATE VIEW V_Problem_Keyword
WITH SCHEMABINDING
AS SELECT kp.ProblemId,p.Title ProblemTitle,p.Reward ProblemReward,k.Used KeyWardAmount
FROM dbo.Keyword2Problem kp JOIN dbo.Problem p
ON kp.ProblemId = p.Id
JOIN dbo.KeyWord k
ON kp.KeywordId = k.Id
GO  
DELETE Keyword2Problem
DROP VIEW V_Problem_Keyword
CREATE UNIQUE CLUSTERED INDEX IX_Problem_ProblemId ON V_Problem_Keyword(ProblemId)
CREATE UNIQUE INDEX IX_Problem_ProblemReward ON V_Problem_Keyword(ProblemReward)
    --6.在基表中插入/删除数据，观察VProblemKeyword是否相应的发生变化
GO
SELECT * FROM Problem
SELECT * FROM KeyWord
SELECT * FROM Keyword2Problem
SELECT * FROM V_Problem_Keyword
BEGIN TRAN 
INSERT Problem VALUES(N'KKK',N'......',1,26,'2019/3/29',N'飞哥',2)
DELETE KeyWord WHERE Id = 1 

    --用户发布一篇悬赏币若干的求助（TProblem），他（TReigister）的帮帮币（BMoney）也会相应减少，
    --但他的帮帮币总额不能少于0分：请综合使用TRY...CATCH和事务完成上述需求。

SELECT * FROM [User]
SELECT * FROM Problem
ALTER TABLE [User] 
ADD BMoney INT CONSTRAINT CK_User_BMoney CHECK (BMoney >= 0)
UPDATE [User] SET BMoney = 20

BEGIN TRANSACTION
    INSERT Problem(Title,Content,Reward,NeedRemoteHelp,PublishDateTime,Author,UserId) 
    Values(N'MM',N'。。。。。。。',16,1,'2021-3-9',N'小胡',3)  
    BEGIN TRY
    UPDATE [User] SET BMoney -= 10 WHERE Id = 3
COMMIT
    END TRY
    BEGIN CATCH
ROLLBACK;
THROW;
    END CATCH

--重复演示各种并发问题，解释/显示锁能否应对上述问题。
CREATE TABLE BangMoney
(
	[Name] NVARCHAR(25),
	Balance INT
)
INSERT BangMoney VALUES(N'李',100);
INSERT BangMoney VALUES(N'肖',100);

DBCC USEROPTIONS
SELECT * FROM BangMoney

--SQL Query1 读未提交
--同一个数据可以被多个事务同时读取，因为读取时不加共享锁，所以在其他事务写时也能同时读
BEGIN TRAN;
UPDATE BangMoney SET Balance = 100;--未提交

ROLLBACK; --回滚后，第二事务中的SELECT沦为 脏读 ，数据库中没有的数据

--SQL Query1 读已提交
--读取时先申请共享锁，所以其他事务写时不能读（避免脏读）；但共享锁得到结果就立即释放，所以之后其他事务可以写（可产生不可重复读）
BEGIN TRAN;
UPDATE BangMoney SET Balance = 100;
COMMIT

--SQL Query1 可重复读
--延长共享锁作用的时间，共享锁一直到事务结束才释放（避免不可重复读），但共享锁的范围仅局限于查找到的数据上（可产生幻影读）
SET TRANSACTION ISOLATION LEVEL	REPEATABLE READ --执行可重复读隔离级别

BEGIN TRANSACTION
SELECT * FROM BangMoney WHERE [Name] = N'肖' -- 第一次读 然后去第二事务更新数据

SELECT * FROM BangMoney WHERE [Name] = N'肖' -- 第二次读 

--SQL Query1 幻影读 隔离级别在REPEATABLE READ中执行
--序列化 共享锁的范围扩展到所有可能影响到该事务的对象（避免了幻影读）
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

BEGIN TRAN; --显示的开启事务
--X锁 UPDATE的现有数据
UPDATE BangMoney SET Balance = 10; --更改整张表
COMMIT

--1.打印如下所示的等腰三角形： 
--      1
--     333
--    55555
--   7777777

PRINT SPACE(3) + '1' + SPACE(3)
PRINT SPACE(2) + '333' + SPACE(2)
PRINT SPACE(1) + '55555' + SPACE(1)
PRINT '7777777'

--2.TProblem中：
--找出所有周末发布的求助（添加CreateTime列，如果还没有的话）
--找出每个作者所有求助悬赏的平均值，精确到小数点后两位
--有一些标题以test、[test]或者Test-开头的求助，找出他们并把这些前缀都换成大写

GO
SELECT * FROM Problem
WHERE (DATEPART(WEEKDAY, PublishDateTime) = 1) or (DATEPART(WEEKDAY, PublishDateTime) = 7)

SELECT Author,ROUND(AVG(Reward),2) FROM Problem GROUP BY Author 

SELECT UPPER(Title) FROM Problem WHERE Title LIKE (N'%test%')          

--3.定义一个函数RANDINT(INT max)，可以取0-max之间的最大值
GO

CREATE FUNCTION RANDINT(@max INT)
RETURNS INT
AS
BEGIN
	RETURN ABS(CHECKSUM(PWDENCRYPT(N''))) --生成有绝对值的随机正数
END

GO
SELECT dbo.RANDINT(100)

SELECT * FROM [USER]
ALTER TABLE [USER]
ADD Invitecode INT
--编写存储过程bp_user_register，模拟一起帮用户注册，包含以下逻辑：
--1.检查用户名是否重复。如果重复，返回错误代码：1
--2.检查用户名密码是否符合“长度不小于4位”的要求。如果不符合，返回错误代码：2
--3.如果有邀请人：
--	检查邀请人是否存在，如果不存在，返回错误代码：10
--	检查邀请码是否正确，如果邀请码不正确，返回错误代码：11
--4.将用户名、密码和邀请人存入数据库（TRegister）
--5.给邀请人增加10个帮帮点积分
--6.通知邀请人（TMessage中生成一条数据）某人使用了他作为邀请人。

DECLARE @u  NVARCHAR(100)
SET @u =  (SELECT UserName FROM [User] WHERE Id = 1)
IF  (@u = N'LS6666') PRINT 'OK'
PRINT @u


GO
ALTER PROCEDURE TRegister 
@username NVARCHAR(20),					 
@password NVARCHAR(20),					 
@inviter INT,  --邀请人（用户名）
@invitecode INT    --邀请码
AS

--SET NOCOUNT ON
SELECT * FROM [User]
IF (EXISTS(SELECT INVITER FROM [User] WHERE INVITER = @inviter ))
	BEGIN
        IF (INVITERCODE = (SELECT INVITERCODE FROM [User] ))--邀请码是否正确
            BEGIN
                IF (EXISTS(SELECT USERNAME FROM [User] WHERE USERNAME = @username ))
                    BEGIN
                        RETURN 1
                    END
                ELSE
                    BEGIN
                        IF (LEN(@password) < 4)
                            BEGIN
                                RETURN 2
                            END
                        ELSE
                            BEGIN
                                INSERT [User](Id, USERNAME,[PASSWORD],INVITER) VALUES (dbo.RANDINT(10000), @username,@password,@inviter)
                                UPDATE [User] SET BMoney += 10 WHERE Id = 2 		 
                            END
                    END
            END
        ELSE
            BEGIN
                RETURN 11
            END
	END
ELSE
	BEGIN
		RETURN 10
	END
--SET NOCOUNT OFF

DECLARE @username NVARCHAR(30),
@password NVARCHAR(20),					 
@inviter NVARCHAR(20),
@invitecode INT


DECLARE @error INT
EXEC @error = dbo.TRegister N'小葵',N'5678',2,8567
SELECT @error
--失败是因为ID列没有自增

SELECT * FROM [User]
SELECT * FROM [Message]

--6.通知邀请人（TMessage中生成一条数据）某人使用了他作为邀请人。
INSERT [Message](Id,FromUser,ToUser,Content,UserId) 
VALUES(2,N'小白',N'小飞',N'小白使用了小飞的邀请码',2); 

ALTER TABLE [Message]
ADD UserId INT

ALTER TABLE [Message] ADD CONSTRAINT FK_User_MessagE_Id FOREIGN KEY(UserId) 
REFERENCES [User](Id)

CREATE TABLE [DBUser]
(
    Id INT IDENTITY(1,1),  
    [UserName] NCHAR(30) ,
    [Password] NCHAR(30) ,
)

INSERT [DBUser] ([UserName],[Password]) VALUES (N'17bang',1234);
INSERT [DBUser] ([UserName]) VALUES (N'Admin');
INSERT [DBUser] ([UserName],[Password]) VALUES (N'Admin-1','   '); 
INSERT [DBUser] ([UserName],[Password]) VALUES (N'SuperAdmin',123456);

DELETE DBUser
SELECT * FROM DBUser

SELECT * FROM Article
INSERT Article(Title,Author) VALUES(N'胖',N'彩虹');
UPDATE Article SET Title = N'瘦' WHERE Author = N'彩虹'  						

ALTER TABLE Article
ADD Id INT IDENTITY(1,1) 

SELECT Title,PublishTime,Author FROM Article WHERE Id = 2

--Article中根据作者进行文章的筛选
SELECT Title,PublishTime,Content FROM Article WHERE Author = N'阳光'

SELECT * FROM BangMoney
UPDATE BangMoney SET Balance -= 5
